package com.vfa.data;

import java.util.ArrayList;

import java.util.Random;

import com.vfa.vocaburay.vfaMain;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.util.Log;

public class WordData {

    private String hand = "Insert by Hand";
    private String importFile = "Import by File";
    public DataHelper dataHelper;
    public WordData(Context context) {
        dataHelper = new DataHelper(context);
        // TODO Auto-generated constructor stub
    }

    //kitvs
    public int insert(String key, String category, String language, String mean, String exam, String read) {
        int categoryId = searchCategoryId(category);
        int languageId = searchLanguageId(language);
        return insert(key,categoryId,languageId,mean,exam,read);
    }
    
    //KitVS: them ham insert nay
    public int insert(String key, int categoryID, int languageID, String mean, String exam, String read) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(DataHelper.COLUMN_WordsKEY_WORD, key);
        contentValues.put(DataHelper.COLUMN_WordsCATEGORY, categoryID);
        contentValues.put(DataHelper.COLUMN_WordsLANGUAGE, languageID);
        contentValues.put(DataHelper.COLUMN_WordsMEAN, mean);
        contentValues.put(DataHelper.COLUMN_WordsRead, read);
        contentValues.put(DataHelper.COLUMN_WordsEXAMPLE, exam);
        contentValues.put(DataHelper.COLUMN_WordsTYPE, hand);
        return (int)dataHelper.db.insert(DataHelper.TABLE_WORDS, null, contentValues);
    }

    /**
     * check key in database
     * @param key
     * @return exist return 1. None return 0;
     */
    public int checkInDatabase(String key) {
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_WORDS, new String[] {DataHelper.COLUMN_WordsKEY_WORD},
                DataHelper.COLUMN_WordsKEY_WORD + " = \"" + key + "\"", null, null, null, null);;
        int count = cursor.getCount();
        cursor.close();
        if(count > 0){
            Log.e("cannc", "This word is existed.");
            return 1;
        }
        return 0;
    }
    public int getWordIDByName(String key) {
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_WORDS, new String[] {DataHelper.COLUMN_WordsID},
                DataHelper.COLUMN_WordsKEY_WORD + " = \"" + key + "\"", null, null, null, null);;
        int count = cursor.getCount();
        int id =-1;
        
        if(count > 0){
        	cursor.moveToFirst();
        	id = cursor.getInt(0);
        }
        cursor.close();
        return id;
    }

    /**
     * get database by Date and key word
     * @param beginDate
     * @param endDate
     * @param key
     * @return a arrayList<Word>
     */
    public ArrayList<Word> getDataBaseByDateAndKey(String beginDate, String endDate, String key) {
        ArrayList<Word> arrayList = new ArrayList<Word>();
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_WORDS, new String[] {DataHelper.COLUMN_WordsID,DataHelper.COLUMN_WordsKEY_WORD,
                DataHelper.COLUMN_WordsMEAN,
                DataHelper.COLUMN_WordsEXAMPLE,
                DataHelper.COLUMN_WordsDATE,
                DataHelper.COLUMN_WordsCATEGORY,
                DataHelper.COLUMN_WordsLANGUAGE,
                DataHelper.COLUMN_WordsRead},
            DataHelper.COLUMN_WordsKEY_WORD + " LIKE \"%" + key + "%\" AND "	+
            DataHelper.COLUMN_WordsDATE +  " >= Datetime('" + beginDate + "') AND " + DataHelper.COLUMN_WordsDATE +  " <= Datetime('" + endDate + "')",
            null, null, null, DataHelper.COLUMN_WordsKEY_WORD + " ASC");
        while (cursor.moveToNext()) {
            Word w = new Word();
            w.setWordID(cursor.getInt(0));
            w.setwordName(cursor.getString(1));
            w.setMeaning(cursor.getString(2));
            w.setExample(cursor.getString(3));
            w.setwordCategory(cursor.getString(5));
            w.setLanguage(cursor.getString(6));
            w.setDate(cursor.getString(4));
            w.setWordRead(cursor.getString(7));
            arrayList.add(w);

        }
        cursor.close();
        return arrayList;
    }

    /**
     * get database by Date
     * @param beginDate
     * @param endDate
     * @return a arrayList<Word>
     */
    public ArrayList<Word> getDatabaseByDate(String beginDate, String endDate) {
        ArrayList<Word> arrayList = new ArrayList<Word>();
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_WORDS, new String[] {DataHelper.COLUMN_WordsID,DataHelper.COLUMN_WordsKEY_WORD,
                DataHelper.COLUMN_WordsMEAN,
                DataHelper.COLUMN_WordsEXAMPLE,
                DataHelper.COLUMN_WordsDATE,
                DataHelper.COLUMN_WordsCATEGORY,
                DataHelper.COLUMN_WordsLANGUAGE,
                DataHelper.COLUMN_WordsRead},
            DataHelper.COLUMN_WordsDATE +  " >= Datetime('" + beginDate + "') AND " + DataHelper.COLUMN_WordsDATE +  " <= Datetime('" + endDate + "')",
            null, null, null, DataHelper.COLUMN_WordsKEY_WORD + " ASC");
        while (cursor.moveToNext()) {
            Word w = new Word();
            w.setWordID(cursor.getInt(0));
            w.setwordName(cursor.getString(1));
            w.setMeaning(cursor.getString(2));
            w.setExample(cursor.getString(3));
            w.setwordCategory(cursor.getString(5));
            w.setLanguage(cursor.getString(6));
            w.setDate(cursor.getString(4));
            w.setWordRead(cursor.getString(7));
            arrayList.add(w);

        }
        cursor.close();
        return arrayList;
    }

    /**
     * Search a word in database.
     * @param key
     * @return ArrayList<Word> is list include all words contain key
     */
    public ArrayList<Word> searchWord(String key) {
    	String fromTables = DataHelper.TABLE_WORDS + " a,"
				+ DataHelper.TABLE_LANGUAGE + " b," + DataHelper.TABLE_CATEGORY
				+ " c";
		String strWhere = "a." + DataHelper.COLUMN_WordsLANGUAGE + " = b."
				+ DataHelper.COLUMN_LanguageID + " and a."
				+ DataHelper.COLUMN_WordsCATEGORY + "= c."
				+ DataHelper.COLUMN_CategoryID;
        Cursor cursor = dataHelper.db.query(fromTables, new String[] {DataHelper.COLUMN_WordsID,DataHelper.COLUMN_WordsKEY_WORD,
                DataHelper.COLUMN_WordsMEAN,
                DataHelper.COLUMN_WordsEXAMPLE,
                DataHelper.COLUMN_WordsDATE,
                DataHelper.COLUMN_CategoryName,
                DataHelper.COLUMN_LanguageName,
                DataHelper.COLUMN_WordsRead},
                strWhere + " and "+
                DataHelper.COLUMN_WordsKEY_WORD + " LIKE \"%" + key + "%\"" + "and a."+ DataHelper.COLUMN_WordsCATEGORY + "=" + DataHelper.COLUMN_CategoryID,
                null, null, null, DataHelper.COLUMN_WordsKEY_WORD + " ASC");;
        ArrayList<Word> arrayList = new ArrayList<Word>();
        while (cursor.moveToNext()) {
        	Word w = new Word();
            w.setWordID(cursor.getInt(0));
            w.setwordName(cursor.getString(1));
            w.setMeaning(cursor.getString(2));
            w.setExample(cursor.getString(3));
            w.setwordCategory(cursor.getString(5));
            w.setLanguage(cursor.getString(6));
            w.setDate(cursor.getString(4));
            w.setWordRead(cursor.getString(7));
            arrayList.add(w);
        }
        cursor.close();
        return arrayList;
    }

    /**
     * get all field of a word
     * @param key
     * @return a Word
     */
    public Word getWord(String key) {
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_WORDS, new String[] {DataHelper.COLUMN_WordsID,DataHelper.COLUMN_WordsKEY_WORD,
                DataHelper.COLUMN_WordsMEAN,
                DataHelper.COLUMN_WordsEXAMPLE,
                DataHelper.COLUMN_WordsDATE,
                DataHelper.COLUMN_WordsCATEGORY,
                DataHelper.COLUMN_WordsLANGUAGE,
                DataHelper.COLUMN_WordsRead},
                DataHelper.COLUMN_WordsKEY_WORD + " = \"" + key + "\"", null, null, null, DataHelper.COLUMN_WordsKEY_WORD + " ASC");;
        Word w = new Word();
        if(cursor.getCount() == 1) {
            cursor.moveToNext();
            w.setWordID(cursor.getInt(0));
            w.setwordName(cursor.getString(1));
            w.setMeaning(cursor.getString(2));
            w.setExample(cursor.getString(3));
            w.setwordCategory(cursor.getString(5));
            w.setLanguage(cursor.getString(6));
            w.setDate(cursor.getString(4));
            w.setWordRead(cursor.getString(7));
        }
        cursor.close();
        return w;
    }

    /**
     * Get all rows of Words table
     * @return Array<Word>
     */
    //kitvs: su dung ket nhieu table
    public ArrayList<Word> getDatabase() {
		String formTables = DataHelper.TABLE_WORDS + " a,"
				+ DataHelper.TABLE_LANGUAGE + " b," + DataHelper.TABLE_CATEGORY
				+ " c";
		String strWhere = "a." + DataHelper.COLUMN_WordsLANGUAGE + " = b."
				+ DataHelper.COLUMN_LanguageID + " and a."
				+ DataHelper.COLUMN_WordsCATEGORY + "= c."
				+ DataHelper.COLUMN_CategoryID;
        Cursor cursor = dataHelper.db.query(formTables, new String[] {DataHelper.COLUMN_WordsID,DataHelper.COLUMN_WordsKEY_WORD,
                DataHelper.COLUMN_WordsMEAN,
                DataHelper.COLUMN_WordsEXAMPLE,
                DataHelper.COLUMN_WordsDATE,
                DataHelper.COLUMN_CategoryName,
                DataHelper.COLUMN_LanguageName,
                DataHelper.COLUMN_WordsRead},
                strWhere, null, null, null, null);
        ArrayList<Word> wordList = new ArrayList<Word>();
        while (cursor.moveToNext()) {
            Word w = new Word();
            w.setWordID(cursor.getInt(0));
            w.setwordName(cursor.getString(1));
            w.setMeaning(cursor.getString(2));
            w.setExample(cursor.getString(3));
            w.setDate(cursor.getString(4));
//            Cursor cursor1 = dataHelper.db.query(DataHelper.TABLE_CATEGORY, new String[] {
//                    DataHelper.COLUMN_CategoryName},
//                    DataHelper.COLUMN_CategoryID + " = " + cursor.getString(5), null, null, null, null);
//            cursor1.moveToFirst();
            w.setwordCategory(cursor.getString(5));
//            Cursor cursor2 = dataHelper.db.query(DataHelper.TABLE_LANGUAGE, new String[] {
//                    DataHelper.COLUMN_LanguageName},
//                    DataHelper.COLUMN_LanguageID + " = " + cursor.getString(6), null, null, null, null);
//            cursor2.moveToFirst();
            w.setLanguage(cursor.getString(6));
            w.setWordRead(cursor.getString(7));
            wordList.add(w);
        //    cursor1.close();
          //  cursor2.close();
        }
        cursor.close();
        return wordList;
    }
    //kivs
    public ArrayList<Word> getWordsLimit(int start, int end) {
//    	String strSelect = "select "+DataHelper.COLUMN_WordsID+","+
//    			DataHelper.COLUMN_WordsKEY_WORD+","+
//    			 DataHelper.COLUMN_WordsMEAN+","+
//    			DataHelper.COLUMN_WordsEXAMPLE+","+
//    			DataHelper.COLUMN_WordsDATE+","+
//    			DataHelper.COLUMN_CategoryName+","+
//    			DataHelper.COLUMN_LanguageName+ "\n";
		String formTables = DataHelper.TABLE_WORDS + " a,"
				+ DataHelper.TABLE_LANGUAGE + " b," + DataHelper.TABLE_CATEGORY
				+ " c";
		String strWhere = "a." + DataHelper.COLUMN_WordsLANGUAGE + " = b."
				+ DataHelper.COLUMN_LanguageID + " and a."
				+ DataHelper.COLUMN_WordsCATEGORY + "= c."
				+ DataHelper.COLUMN_CategoryID;
		String limit = " LIMIT "+start+","+ end ;
        Cursor cursor = dataHelper.db.query(formTables, new String[] {DataHelper.COLUMN_WordsID,DataHelper.COLUMN_WordsKEY_WORD,
                DataHelper.COLUMN_WordsMEAN,
                DataHelper.COLUMN_WordsEXAMPLE,
                DataHelper.COLUMN_WordsDATE,
                DataHelper.COLUMN_CategoryName,
                DataHelper.COLUMN_LanguageName,
                DataHelper.COLUMN_WordsRead},
                strWhere, null, null, null, null," "+start+","+end+"");
        ArrayList<Word> wordList = new ArrayList<Word>();
        while (cursor.moveToNext()) {
            Word w = new Word();
            w.setWordID(cursor.getInt(0));
            w.setwordName(cursor.getString(1));
            w.setMeaning(cursor.getString(2));
            w.setExample(cursor.getString(3));
            w.setDate(cursor.getString(4));
            w.setwordCategory(cursor.getString(5));
            w.setLanguage(cursor.getString(6));
            w.setWordRead(cursor.getString(7));
            wordList.add(w);
        }
        cursor.close();
        return wordList;
    }
//backup====================================================================================
//    public ArrayList<String> getAllWordsToBackup() {
//        ArrayList<String> arrayList = new ArrayList<String>();
//        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_WORDS, new String[] {DataHelper.COLUMN_WordsID,
//                DataHelper.COLUMN_WordsKEY_WORD,
//                DataHelper.COLUMN_WordsCATEGORY,
//                DataHelper.COLUMN_WordsLANGUAGE,
//                DataHelper.COLUMN_WordsMEAN,
//                DataHelper.COLUMN_WordsEXAMPLE,
//                DataHelper.COLUMN_WordsDATE,
//                DataHelper.COLUMN_WordsTYPE,
//                DataHelper.COLUMN_WordsRead
//                },
//            null, null, null, null, null);
//        while (cursor.moveToNext()) {
//            String string = "\"" + cursor.getString(0) + "\";\"" + cursor.getString(1) + "\";\"" + cursor.getString(2) + "\";\"" + cursor.getString(3) + "\";\"" + cursor.getString(4) + "\";\"" + cursor.getString(5) + "\";\"" + cursor.getString(6) + "\";\"" + cursor.getString(7) + "\"";
//            arrayList.add(string);
//        }
//        cursor.close();
//        return arrayList;
//    }

    public ArrayList<String> getAllLanguageToBackup() {
        ArrayList<String> arrayList = new ArrayList<String>();
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_LANGUAGE, new String[] {DataHelper.COLUMN_LanguageID,
                DataHelper.COLUMN_LanguageName},
            null, null, null, null, null);
        while (cursor.moveToNext()) {
            String string = "\"" + cursor.getString(0) + "\";\"" + cursor.getString(1) + "\"";
            arrayList.add(string);
        }
        cursor.close();
        return arrayList;
    }
    
    
    
    // them moi ngay luc 3:46 2 thang 11 nm 2011 ; lay ra tat cac cac ngon ngu trong database
    public ArrayList<String> getAllLanguage() {
        ArrayList<String> arrayList = new ArrayList<String>();
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_LANGUAGE, new String[] {DataHelper.COLUMN_LanguageID,
                DataHelper.COLUMN_LanguageName},
            null, null, null, null, null);
        while (cursor.moveToNext()) {
            String string =cursor.getString(1);
            arrayList.add(string);
        }
        cursor.close();
        return arrayList;
    }//end

    public ArrayList<String> getAllCategoryToBackup() {
        ArrayList<String> arrayList = new ArrayList<String>();
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_CATEGORY, new String[] {DataHelper.COLUMN_CategoryID,
                DataHelper.COLUMN_CategoryName},
            null, null, null, null, null);
        while (cursor.moveToNext()) {
            String string = "\"" + cursor.getString(0) + "\";\"" + cursor.getString(1) + "\"";
            arrayList.add(string);
        }
        cursor.close();
        return arrayList;
    }

    /**
     * Update a word in database. If it's not existed, insert it into database.
     * @param key
     * @param category
     * @param language
     * @param mean
     * @param exam
     */
    public int update(int id,String key, String category, String language, String mean, String exam, String read) {
        if(key == null) return -1;
        int categoryId = searchCategoryId(category);
        int languageId = searchLanguageId(language);
        ContentValues contentValues = new ContentValues();
        contentValues.put(DataHelper.COLUMN_WordsKEY_WORD, key);
        contentValues.put(DataHelper.COLUMN_WordsCATEGORY, categoryId);
        contentValues.put(DataHelper.COLUMN_WordsLANGUAGE, languageId);
        contentValues.put(DataHelper.COLUMN_WordsMEAN, mean);
        contentValues.put(DataHelper.COLUMN_WordsRead, read);
        contentValues.put(DataHelper.COLUMN_WordsEXAMPLE, exam);
        return dataHelper.db.update(DataHelper.TABLE_WORDS, contentValues, DataHelper.COLUMN_WordsID + " = " + id, null);
    }


//    /**
//     * Delete a word
//     * @param key
//     */
//    public void delete(String key) {
//        String DELETE = "delete from " + DataHelper.TABLE_WORDS + " where " + DataHelper.COLUMN_WordsKEY_WORD + " = " + key + "\"";
//        dataHelper.db.execSQL(DELETE);
//    }
    /**
     * Delete a word
     * @param id
     */
    //kitvs: sua them hasm delete bang id
    public int delete(int id) {
        //String DELETE = "delete from " + DataHelper.TABLE_WORDS + " where " + DataHelper.COLUMN_WordsID + " = " + id + "\"";
        //dataHelper.db.execSQL(DELETE);
    	return dataHelper.db.delete(DataHelper.TABLE_WORDS, DataHelper.COLUMN_WordsID+ " = " + id , null);
    }
    /**
     * Get category of word such as: Verb, Adjective,...
     * @param key
     * @return a string
     */
    public String getInstanceFromID(String key) {
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_WORDS, new String[] {DataHelper.COLUMN_WordsCATEGORY},
                DataHelper.COLUMN_WordsKEY_WORD + " == \"" + key + "\"", null, null, null, null);
        if(cursor.moveToFirst()) {
            return cursor.getString(0);
        }
        return null;
    }

    /**
     * Get category of word such as: Verb, Adjective,...
     * @param key
     * @return a string
     */
    public String getMeanOf(String key) {
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_WORDS, new String[] {DataHelper.COLUMN_WordsMEAN},
                DataHelper.COLUMN_WordsKEY_WORD + " == \"" + key + "\"", null, null, null, null);
        if(cursor.moveToFirst()) {
            return cursor.getString(0);
        }
        return null;
    }

    /**
     * get row number of table Words
     * @return row number
     */
    public int getRowNumber(String languageName, String categoryId, String startDate, String endDate) {
        String language = vfaMain.languageData.getLanguageIdByName(languageName);
        String sqlQuery = DataHelper.COLUMN_WordsLANGUAGE + " = '" + language + "'";
        if(!categoryId.equalsIgnoreCase("")){
            sqlQuery += " AND " + DataHelper.COLUMN_WordsCATEGORY + " = '" + categoryId + "'";
        }
        if(!startDate.equals("")){
            sqlQuery += " AND " + DataHelper.COLUMN_WordsDATE + " >= '" + startDate + "'";
        }
        if(!endDate.equals("")){
            sqlQuery += " AND " + DataHelper.COLUMN_WordsDATE + " <= '" + endDate + " 23:59:59'";
        }
        int rowNumber = 0;
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_WORDS, new String[] {DataHelper.COLUMN_WordsMEAN},	sqlQuery, null, null, null, null);
        rowNumber = cursor.getCount();
        cursor.close();
        return rowNumber;
    }

    public int getRowNumber() {
        int rowNumber = 0;
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_WORDS, new String[] {DataHelper.COLUMN_WordsMEAN}, null, null, null, null, null);
        rowNumber = cursor.getCount();
        cursor.close();
        return rowNumber;
    }

    public int getRowNumberByHand(String languageName, String categoryId, String startDate, String endDate) {
        String language = vfaMain.languageData.getLanguageIdByName(languageName);
        String sqlQuery = DataHelper.COLUMN_WordsTYPE + " = '" + hand + "' AND " +
                DataHelper.COLUMN_WordsLANGUAGE + " = '" + language + "'";
        if(!categoryId.equalsIgnoreCase("")){
            sqlQuery += " AND " + DataHelper.COLUMN_WordsCATEGORY + " = '" + categoryId + "'";
        }
        if(!startDate.equals("")){
            sqlQuery += " AND " + DataHelper.COLUMN_WordsDATE + " >= '" + startDate + "'";
        }
        if(!endDate.equals("")){
            sqlQuery += " AND " + DataHelper.COLUMN_WordsDATE + " <= '" + endDate + " 23:59:59'";
        }
        Log.e("quyenlm", sqlQuery);
        int rowNumber = 0;
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_WORDS, new String[] {DataHelper.COLUMN_WordsKEY_WORD},
                sqlQuery,
                null, null, null, null);
            rowNumber = cursor.getCount();
            cursor.close();

        return rowNumber;

    }

    public int getRowNumberByFile(String languageName, String categoryId, String startDate, String endDate) {
        String language = vfaMain.languageData.getLanguageIdByName(languageName);
        String sqlQuery = DataHelper.COLUMN_WordsTYPE + " = '" + importFile + "' AND " +
                DataHelper.COLUMN_WordsLANGUAGE + " = '" + language + "'";
        if(!categoryId.equalsIgnoreCase("")){
            sqlQuery += " AND " + DataHelper.COLUMN_WordsCATEGORY + " = '" + categoryId + "'";
        }
        if(!startDate.equals("")){
            sqlQuery += " AND " + DataHelper.COLUMN_WordsDATE + " >= '" + startDate + "'";
        }
        if(!endDate.equals("")){
            sqlQuery += " AND " + DataHelper.COLUMN_WordsDATE + " <= '" + endDate + " 23:59:59'";
        }
        Log.e("quyenlm", sqlQuery);
        int rowNumber = 0;
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_WORDS, new String[] {DataHelper.COLUMN_WordsKEY_WORD},
                sqlQuery,
                null, null, null, null);
            rowNumber = cursor.getCount();
            cursor.close();

        return rowNumber;
    }

    /**
     * get random n question
     * @param number
     * @return a arrayList<Question>
     */
    public ArrayList<Question> getRandomWord(int number) {
        Random random = new Random();
        String[] order = {DataHelper.COLUMN_WordsMEAN + " DESC",
                DataHelper.COLUMN_WordsMEAN + " ASC",
                DataHelper.COLUMN_WordsEXAMPLE + " DESC",
                DataHelper.COLUMN_WordsEXAMPLE + " ASC", null};
        int chooseOderType = random.nextInt(order.length);
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_WORDS, new String[] {DataHelper.COLUMN_WordsKEY_WORD,
                DataHelper.COLUMN_WordsMEAN}, null, null, null, null, order[chooseOderType]);
        int mLimit = 0;
        ArrayList<Question> questionList = new ArrayList<Question>();
        while(cursor.moveToNext() && mLimit < number) {
            int randomPosition = random.nextInt(4);
            String[] answer = getRandomAnswer(cursor.getString(1));

            switch (randomPosition) {
            case 0:
                questionList.add(new Question(cursor.getString(0), cursor.getString(1), answer[0], answer[1], answer[2], cursor.getString(1)));
                break;
            case 1:
                questionList.add(new Question(cursor.getString(0), answer[0], cursor.getString(1), answer[1], answer[2], cursor.getString(1)));
                break;
            case 2:
                questionList.add(new Question(cursor.getString(0), answer[0], answer[1], cursor.getString(1), answer[2], cursor.getString(1)));
                break;
            case 3:
                questionList.add(new Question(cursor.getString(0), answer[0], answer[1], answer[2], cursor.getString(1), cursor.getString(1)));
                break;
            default:
                break;
            }
            mLimit++;
        }
        cursor.close();
        return questionList;
    }

    /**
     * get random database by input type and language, with limit row is max
     * @param inputType
     * @param language
     * @param max
     * @return a arrayList<Word>
     */
    public ArrayList<Question> getRandomDataBaseByTypeAndLanguage(int inputType, String languageName, int max, String categoryId) {
        String catSQL = " = '" + categoryId + "'";
        if(categoryId.equalsIgnoreCase("")){
            catSQL = " > 0";
        }
        String language = vfaMain.languageData.getLanguageIdByName(languageName);
        Random random = new Random();
        String[] order = {DataHelper.COLUMN_WordsMEAN + " DESC",
                DataHelper.COLUMN_WordsMEAN + " ASC",
                DataHelper.COLUMN_WordsEXAMPLE + " DESC",
                DataHelper.COLUMN_WordsEXAMPLE + " ASC", null};
        int chooseOderType = random.nextInt(order.length);
        Cursor cursor;
        if(inputType == 0) {
            cursor = dataHelper.db.query(DataHelper.TABLE_WORDS, new String[] {DataHelper.COLUMN_WordsKEY_WORD,
                    DataHelper.COLUMN_WordsMEAN,
                    DataHelper.COLUMN_WordsEXAMPLE,
                    DataHelper.COLUMN_WordsDATE,
                    DataHelper.COLUMN_WordsCATEGORY,
                    DataHelper.COLUMN_WordsLANGUAGE,
            		DataHelper.COLUMN_WordsRead},
                DataHelper.COLUMN_WordsLANGUAGE + " = '" + language + "' AND " +
                DataHelper.COLUMN_WordsCATEGORY + catSQL,
                null, null, null, order[chooseOderType]);
        }
        else if(inputType == 1) {
            cursor = dataHelper.db.query(DataHelper.TABLE_WORDS, new String[] {DataHelper.COLUMN_WordsKEY_WORD,
                    DataHelper.COLUMN_WordsMEAN,
                    DataHelper.COLUMN_WordsEXAMPLE,
                    DataHelper.COLUMN_WordsDATE,
                    DataHelper.COLUMN_WordsCATEGORY,
                    DataHelper.COLUMN_WordsLANGUAGE,
                    DataHelper.COLUMN_WordsRead},
                DataHelper.COLUMN_WordsLANGUAGE + " = '" + language + "' AND "	+
                DataHelper.COLUMN_WordsTYPE + " = '" + hand + "' AND " +
                DataHelper.COLUMN_WordsCATEGORY + catSQL,
                null, null, null, order[chooseOderType]);
        }
        else { // inputType == 2
            cursor = dataHelper.db.query(DataHelper.TABLE_WORDS, new String[] {DataHelper.COLUMN_WordsKEY_WORD,
                    DataHelper.COLUMN_WordsMEAN,
                    DataHelper.COLUMN_WordsEXAMPLE,
                    DataHelper.COLUMN_WordsDATE,
                    DataHelper.COLUMN_WordsCATEGORY,
                    DataHelper.COLUMN_WordsLANGUAGE,
                    DataHelper.COLUMN_WordsRead},
                DataHelper.COLUMN_WordsLANGUAGE + " = '" + language + "' AND "	+
                DataHelper.COLUMN_WordsTYPE + " = '" + importFile + "' AND " +
                DataHelper.COLUMN_WordsCATEGORY + catSQL,
                null, null, null, order[chooseOderType]);
        }
        int mLimit = 0;
        if(max == 0) {
            ArrayList<Question> questionList = new ArrayList<Question>();
            while(cursor.moveToNext()) {
                int randomPosition = random.nextInt(4);
                String[] answer = getRandomAnswer(cursor.getString(1));

                switch (randomPosition) {
                case 0:
                    questionList.add(new Question(cursor.getString(0), cursor.getString(1), answer[0], answer[1], answer[2], cursor.getString(1)));
                    break;
                case 1:
                    questionList.add(new Question(cursor.getString(0), answer[0], cursor.getString(1), answer[1], answer[2], cursor.getString(1)));
                    break;
                case 2:
                    questionList.add(new Question(cursor.getString(0), answer[0], answer[1], cursor.getString(1), answer[2], cursor.getString(1)));
                    break;
                case 3:
                    questionList.add(new Question(cursor.getString(0), answer[0], answer[1], answer[2], cursor.getString(1), cursor.getString(1)));
                    break;
                default:
                    break;
                }
                mLimit++;
            }
            cursor.close();
            return questionList;
        }
        else {
            ArrayList<Question> questionList = new ArrayList<Question>();
            while(cursor.moveToNext() && mLimit < max) {
                int randomPosition = random.nextInt(4);
                String[] answer = getRandomAnswer(cursor.getString(1));

                switch (randomPosition) {
                case 0:
                    questionList.add(new Question(cursor.getString(0), cursor.getString(1), answer[0], answer[1], answer[2], cursor.getString(1)));
                    break;
                case 1:
                    questionList.add(new Question(cursor.getString(0), answer[0], cursor.getString(1), answer[1], answer[2], cursor.getString(1)));
                    break;
                case 2:
                    questionList.add(new Question(cursor.getString(0), answer[0], answer[1], cursor.getString(1), answer[2], cursor.getString(1)));
                    break;
                case 3:
                    questionList.add(new Question(cursor.getString(0), answer[0], answer[1], answer[2], cursor.getString(1), cursor.getString(1)));
                    break;
                default:
                    break;
                }
                mLimit++;
            }
            cursor.close();
            return questionList;
        }
    }
    public int getQuizIDbyName(String quizname){
    	Cursor cursor = null;
    	if(quizname !="")
    	{
	    	cursor = dataHelper.db.query(DataHelper.TABLE_QUIZ, new String[] {DataHelper.COLUMN_QuizID},
	                DataHelper.COLUMN_QuizName + " = '" + quizname + "'", null, null, null, null);
    	}
       int quizid = -1;
        if(cursor.getCount() > 0) {
            cursor.moveToFirst();
            return cursor.getInt(0);
        }
        else
        {
        	return quizid;
        }
    }
    public ArrayList<Question> getRandomDataBaseByTypeAndLanguage(int inputType, String languageName, int max, String categoryId,String Quizname) {
        String catSQL = " = '" + categoryId + "'";
        if(categoryId.equalsIgnoreCase("")){
            catSQL = " > 0";
        }
        int QuizId = getQuizIDbyName(Quizname);
        String language = vfaMain.languageData.getLanguageIdByName(languageName);
        Random random = new Random();
        String[] order = {DataHelper.COLUMN_WordsMEAN + " DESC",
                DataHelper.COLUMN_WordsMEAN + " ASC",
                DataHelper.COLUMN_WordsEXAMPLE + " DESC",
                DataHelper.COLUMN_WordsEXAMPLE + " ASC", null};
        int chooseOderType = random.nextInt(order.length);
        Cursor cursor;
        String strTable = DataHelper.TABLE_WORDS+ " a, "+ DataHelper.TABLE_QUIZWORDS+ " b";
        if(inputType == 0) {
        	
            cursor = dataHelper.db.query(strTable , new String[] {DataHelper.COLUMN_WordsKEY_WORD,
                    DataHelper.COLUMN_WordsMEAN,
                    DataHelper.COLUMN_WordsEXAMPLE,
                    DataHelper.COLUMN_WordsDATE,
                    DataHelper.COLUMN_WordsCATEGORY,
                    DataHelper.COLUMN_WordsLANGUAGE},
                    "b."+ DataHelper.COLUMN_QuizWords_WordsID + "=" + "a." + DataHelper.COLUMN_WordsID+ " and "+
                    "b." + DataHelper.COLUMN_QuizWord_QuizID +  "=" + QuizId + " and "+
                DataHelper.COLUMN_WordsLANGUAGE + " = '" + language + "' AND " +
                DataHelper.COLUMN_WordsCATEGORY + catSQL,
                null, null, null, order[chooseOderType]);
        }
        else if(inputType == 1) {
        	//String strTable = DataHelper.TABLE_WORDS+ " a, "+ DataHelper.TABLE_QUIZWORDS+ " b";
            cursor = dataHelper.db.query(strTable, new String[] {DataHelper.COLUMN_WordsKEY_WORD,
                    DataHelper.COLUMN_WordsMEAN,
                    DataHelper.COLUMN_WordsEXAMPLE,
                    DataHelper.COLUMN_WordsDATE,
                    DataHelper.COLUMN_WordsCATEGORY,
                    DataHelper.COLUMN_WordsLANGUAGE},
                    "b."+ DataHelper.COLUMN_QuizWords_WordsID + "=" + "a." + DataHelper.COLUMN_WordsID+ " and "+
                            "b." + DataHelper.COLUMN_QuizWord_QuizID +  "=" + QuizId + " and "+
                DataHelper.COLUMN_WordsLANGUAGE + " = '" + language + "' AND "	+
                DataHelper.COLUMN_WordsTYPE + " = '" + hand + "' AND " +
                DataHelper.COLUMN_WordsCATEGORY + catSQL,
                null, null, null, order[chooseOderType]);
        }
        else { // inputType == 2
            cursor = dataHelper.db.query(strTable, new String[] {DataHelper.COLUMN_WordsKEY_WORD,
                    DataHelper.COLUMN_WordsMEAN,
                    DataHelper.COLUMN_WordsEXAMPLE,
                    DataHelper.COLUMN_WordsDATE,
                    DataHelper.COLUMN_WordsCATEGORY,
                    DataHelper.COLUMN_WordsLANGUAGE},
                    "b."+ DataHelper.COLUMN_QuizWords_WordsID + "=" + "a." + DataHelper.COLUMN_WordsID+ " and "+
                            "b." + DataHelper.COLUMN_QuizWord_QuizID +  "=" + QuizId + " and "+
                DataHelper.COLUMN_WordsLANGUAGE + " = '" + language + "' AND "	+
                DataHelper.COLUMN_WordsTYPE + " = '" + importFile + "' AND " +
                DataHelper.COLUMN_WordsCATEGORY + catSQL,
                null, null, null, order[chooseOderType]);
        }
        int mLimit = 0;
        if(max == 0) {
            ArrayList<Question> questionList = new ArrayList<Question>();
            while(cursor.moveToNext()) {
                int randomPosition = random.nextInt(4);
                String[] answer = getRandomAnswer(cursor.getString(1));

                switch (randomPosition) {
                case 0:
                    questionList.add(new Question(cursor.getString(0), cursor.getString(1), answer[0], answer[1], answer[2], cursor.getString(1)));
                    break;
                case 1:
                    questionList.add(new Question(cursor.getString(0), answer[0], cursor.getString(1), answer[1], answer[2], cursor.getString(1)));
                    break;
                case 2:
                    questionList.add(new Question(cursor.getString(0), answer[0], answer[1], cursor.getString(1), answer[2], cursor.getString(1)));
                    break;
                case 3:
                    questionList.add(new Question(cursor.getString(0), answer[0], answer[1], answer[2], cursor.getString(1), cursor.getString(1)));
                    break;
                default:
                    break;
                }
                mLimit++;
            }
            cursor.close();
            return questionList;
        }
        else {
            ArrayList<Question> questionList = new ArrayList<Question>();
            while(cursor.moveToNext() && mLimit < max) {
                int randomPosition = random.nextInt(4);
                String[] answer = getRandomAnswer(cursor.getString(1));

                switch (randomPosition) {
                case 0:
                    questionList.add(new Question(cursor.getString(0), cursor.getString(1), answer[0], answer[1], answer[2], cursor.getString(1)));
                    break;
                case 1:
                    questionList.add(new Question(cursor.getString(0), answer[0], cursor.getString(1), answer[1], answer[2], cursor.getString(1)));
                    break;
                case 2:
                    questionList.add(new Question(cursor.getString(0), answer[0], answer[1], cursor.getString(1), answer[2], cursor.getString(1)));
                    break;
                case 3:
                    questionList.add(new Question(cursor.getString(0), answer[0], answer[1], answer[2], cursor.getString(1), cursor.getString(1)));
                    break;
                default:
                    break;
                }
                mLimit++;
            }
            cursor.close();
            return questionList;
        }
    }

    private String[] getRandomAnswer(String rightAnswer) {
        String string1 = "";
        String string2 = "";
        String string3 = "";
        Random random = new Random();
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_WORDS, new String[] {DataHelper.COLUMN_WordsMEAN},
                null, null, null, null, null);
        int countRows = cursor.getCount();
        int p = 0;
        while(true) {
            p = random.nextInt(countRows);
            cursor.moveToPosition(p);
            if(!cursor.getString(0).equals(rightAnswer)) {
                string1 = cursor.getString(0);
                break;
            }
        }

        while(true) {
            p = random.nextInt(countRows);
            cursor.moveToPosition(p);
            if(!cursor.getString(0).equals(rightAnswer) && !cursor.getString(0).equals(string1)) {
                string2 = cursor.getString(0);
                break;
            }
        }
        while(true) {
            p = random.nextInt(countRows);
            cursor.moveToPosition(p);
            if(!cursor.getString(0).equals(rightAnswer) && !cursor.getString(0).equals(string1) && !cursor.getString(0).equals(string2)) {
                string3 = cursor.getString(0);
                break;
            }
        }
        cursor.close();
        String[] answer = {string1, string2, string3};
        Log.e("cannc", string1 + ", " + string2 + ", " + string3 + ", " + rightAnswer);
        return answer;
    }

    public void insertHistory(String username, String topic, String questionkey, String answer1, String answer2, String answer3, String answer4, String rightanswer, String result) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(DataHelper.COLUMN_HistoryUserName, username);
        contentValues.put(DataHelper.COLUMN_HistoryTopic, topic);
        contentValues.put(DataHelper.COLUMN_HistoryKeyQuestion, questionkey);
        contentValues.put(DataHelper.COLUMN_HistoryAnswer1, answer1);
        contentValues.put(DataHelper.COLUMN_HistoryAnswer2, answer2);
        contentValues.put(DataHelper.COLUMN_HistoryAnswer3, answer3);
        contentValues.put(DataHelper.COLUMN_HistoryAnswer4, answer4);
        contentValues.put(DataHelper.COLUMN_HistoryRightAnswer, rightanswer);
        contentValues.put(DataHelper.COLUMN_HistoryResult, result);
        dataHelper.db.insert(DataHelper.TABLE_HistoryTest, null, contentValues);
    }

    public ArrayList<Question> getHistoryByTopic(String topic) {
        ArrayList<Question> arrayList = new ArrayList<Question>();
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_HistoryTest, new String[] {DataHelper.COLUMN_HistoryUserName,
                DataHelper.COLUMN_HistoryTopic,
                DataHelper.COLUMN_HistoryKeyQuestion,
                DataHelper.COLUMN_HistoryAnswer1,
                DataHelper.COLUMN_HistoryAnswer2,
                DataHelper.COLUMN_HistoryAnswer3,
                DataHelper.COLUMN_HistoryAnswer4,
                DataHelper.COLUMN_HistoryRightAnswer}, DataHelper.COLUMN_HistoryTopic + " = '" + topic + "'",
                null, null, null, null);
        while (cursor.moveToNext()) {
            arrayList.add(new Question(cursor.getString(2), cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6), cursor.getString(7)));
        }
        cursor.close();
        return arrayList;
    }

    public ArrayList<Question> getHistoryByDate(String beginDate, String endDate) {
        ArrayList<Question> arrayList = new ArrayList<Question>();
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_HistoryTest, new String[] {DataHelper.COLUMN_HistoryUserName,
                DataHelper.COLUMN_HistoryTopic,
                DataHelper.COLUMN_HistoryKeyQuestion,
                DataHelper.COLUMN_HistoryAnswer1,
                DataHelper.COLUMN_HistoryAnswer2,
                DataHelper.COLUMN_HistoryAnswer3,
                DataHelper.COLUMN_HistoryAnswer4,
                DataHelper.COLUMN_HistoryRightAnswer},
                DataHelper.COLUMN_HistoryDataTest +  " >= Datetime('" + beginDate + "') AND " + DataHelper.COLUMN_HistoryDataTest +  " <= Datetime('" + endDate + "')",
                null, null, null, null);
        while (cursor.moveToNext()) {
            arrayList.add(new Question(cursor.getString(2), cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6), cursor.getString(7)));
        }
        cursor.close();
        return arrayList;
    }

    public void insertUser(String username) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(DataHelper.COLUMN_UserName, username);
        dataHelper.db.insert(DataHelper.TABLE_USERS, null, contentValues);
    }

    public void insertCategory(String category) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(DataHelper.COLUMN_CategoryName, category);
        dataHelper.db.insert(DataHelper.TABLE_CATEGORY, null, contentValues);
    }
    /**
     * search category. Ff not found, insert it into category table
     * @param category
     * @return id of category
     */
    public int searchCategoryId(String category) {
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_CATEGORY, new String[] {DataHelper.COLUMN_CategoryID},
                DataHelper.COLUMN_CategoryName + " = '" + category + "'", null, null, null, null);
        int id = -1;
        if(!cursor.moveToFirst()) {
            cursor.close();
            insertCategory(category);
            Cursor cursor1 = dataHelper.db.query(DataHelper.TABLE_CATEGORY, new String[] {DataHelper.COLUMN_CategoryID},
                    DataHelper.COLUMN_CategoryName + " = '" + category + "'", null, null, null, null);
            cursor1.moveToFirst();
            id = cursor1.getInt(0);
            cursor1.close();

        }
        else {
            id = cursor.getInt(0);
            cursor.close();
        }
        return id;
    }

    public void insertLanguage(String language) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(DataHelper.COLUMN_LanguageName, language);
        dataHelper.db.insert(DataHelper.TABLE_LANGUAGE, null, contentValues);
    }
    /**
     * Search language. If not found, insert it into language table
     * @param language
     * @return id of language
     */
    public int searchLanguageId(String language) {
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_LANGUAGE, new String[] {DataHelper.COLUMN_LanguageID},
                DataHelper.COLUMN_LanguageName + " = '" + language + "'", null, null, null, null);
        int id = -1;
        if(!cursor.moveToFirst()) {
            cursor.close();
            insertLanguage(language);
            Cursor cursor1 = dataHelper.db.query(DataHelper.TABLE_LANGUAGE, new String[] {DataHelper.COLUMN_LanguageID},
                    DataHelper.COLUMN_LanguageName + " = '" + language + "'", null, null, null, null);
            cursor1.moveToFirst();
            id = cursor1.getInt(0);
            cursor1.close();
        }
        else {
            id = cursor.getInt(0);
            cursor.close();
        }
        return id;
    }

    /**
     * Get all rows of Category table
     * @return Array<Category>
     */
    public ArrayList<Category> getCategories() {
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_CATEGORY, new String[] {DataHelper.COLUMN_CategoryID,
        DataHelper.COLUMN_CategoryName},
        null, null, null, null, DataHelper.COLUMN_CategoryID);
        ArrayList<Category> categoryList = new ArrayList<Category>();
        while (cursor.moveToNext()) {
            Category c = new Category();
            c.setCategoryID(cursor.getInt(0));
            c.setCategoryName(cursor.getString(1));
            categoryList.add(c);
        }
        cursor.close();
        return categoryList;
    }

    /**
     * get category. Ff not found, insert it into category table
     * @param category
     * @return id of category
     */
    public String getCategoryIdByName(String categoryName) {
        Cursor cursor = dataHelper.db.query(DataHelper.TABLE_CATEGORY, new String[] {DataHelper.COLUMN_CategoryID},
                DataHelper.COLUMN_CategoryName + " = '" + categoryName + "'", null, null, null, null);
        String categoryId = "";
        if(cursor.moveToFirst()) {
            categoryId = cursor.getString(0);
        }
        return categoryId;
    }
 // them moi ngay luc 1:15 2 thang 11 nm 2011
	public ArrayList<String> getWordsToBackupByLanguage(String language) {

		String langid = getLanguageIDbyLanguage(language);

		ArrayList<String> arrayList = new ArrayList<String>();

		String sql = "Select  " + DataHelper.COLUMN_WordsKEY_WORD + " , "
				+ DataHelper.COLUMN_WordsCATEGORY + " , "
				+ DataHelper.COLUMN_WordsLANGUAGE + " , "
				+ DataHelper.COLUMN_WordsMEAN + " , "
				+ DataHelper.COLUMN_WordsEXAMPLE + " , "
				+ DataHelper.COLUMN_WordsRead 
				+ " from "
				+ DataHelper.TABLE_WORDS + " where "
				+ DataHelper.COLUMN_WordsLANGUAGE + " = '" + langid + "'";
		Cursor cursor = dataHelper.db.rawQuery(sql, null);
		System.out.println("cursor.getCount()   " + cursor.getCount());
		while (cursor.moveToNext()) {
			// String string = "\"" + cursor.getString(0) + "\",\"" +
			// cursor.getString(1) + "\",\"" + cursor.getString(2) + "\",\"" +
			// cursor.getString(3) + "\",\"" + cursor.getString(4) + "\"";
			String category = "Other", exam;
			category = getCategoryByID(cursor.getString(1));
			exam = cursor.getString(4).trim().replaceAll("\"", "\"\"");

			String string = "\"" + cursor.getString(0).trim() + "\",\""
					+ category.trim() + "\",\"" + language.trim() + "\",\""
					+ cursor.getString(3).trim() + "\",\""+cursor.getString(5).trim() + "\",\"" + exam + "\"";
			arrayList.add(string);
		}
		cursor.close();
		return arrayList;
	}

 // them moi ngay luc 1:15 2 thang 11 nm 2011
    public String getLanguageIDbyLanguage(String language) {
        String category="";
       
        String sql = "Select "+ DataHelper.COLUMN_LanguageID
        		+ " from "+DataHelper.TABLE_LANGUAGE
        		+" where "+DataHelper.COLUMN_LanguageName+" = '"+language+"'";
        Cursor cursor = dataHelper.db.rawQuery(sql, null);
        
        while (cursor.moveToNext()) {
        	category = cursor.getString(0);
        }
        
        cursor.close();
        return category;
    }
    
    
 // them moi ngay luc 1:15 2 thang 11 nm 2011
    public String getCategoryByID(String id) {
        String category="";
       
        String sql = "Select "+ DataHelper.COLUMN_CategoryName
        		+ " from "+DataHelper.TABLE_CATEGORY
        		+" where "+DataHelper.COLUMN_CategoryID+" = '"+id+"'";
        Cursor cursor = dataHelper.db.rawQuery(sql, null);
        
        while (cursor.moveToNext()) {
        	category = cursor.getString(0);
        }
        
        cursor.close();
        return category;
    }
    
    public  ArrayList<String> getQuizName(){
    	ArrayList<String> QuizName= new ArrayList<String>();
    	String sql = "Select "+DataHelper.COLUMN_QuizName+ " from "+ DataHelper.TABLE_QUIZ;
    	Cursor cursor = dataHelper.db.rawQuery(sql, null);
    	while(cursor.moveToNext()){
    		String QuizName1 = cursor.getString(0);
    		QuizName.add(QuizName1);
    	}
    	cursor.close();
    	return QuizName;
    }
    

}
